package com.example.demo.testSQL;

public class TestSQL {

-- 考勤数据
    drop table if exists temp.d_ihd_hemp_account_emp_score_day_dtl_temp03;
    create table temp.d_ihd_hemp_account_emp_score_day_dtl_temp03 STORED AS parquet as
    with a1 as (
            select
                    data_dt
            ,emp_cd
            ,emp_nam
            ,leave_typ
            ,leave_type_desc  as leave_typ_desc
            ,apply_sts
            ,apply_sts_desc
            ,leave_dur_hours
            ,leave_time
            ,handover
            ,handover_work
            ,data_week
            ,cast(row_number() over(partition by data_dt,emp_cd order by leave_time asc) as string) as rn
    ,concat(''{"leave_typ":"'',leave_typ,
        ''","leave_typ_desc":"'',leave_type_desc,
                ''","leave_time":"'',leave_time,
                ''","leave_dur_hours":"'',cast(cast(leave_dur_hours as decimal(18,1)) as string),
                ''","leave_apply_sts":"'',apply_sts,
                ''","leave_apply_sts_desc":"'',apply_sts_desc,
                ''","handover":"'',handover,
                ''","reason":"'',replace(reason,''"'',''“''),
        ''","approver":'',''null'',
        ''}'') as day_leave_info
    from dw.d_ihd_hat_hrx_leave_day_dtl
    where  data_dt >= (select min(data_dt) from temp.d_ihd_hemp_account_emp_score_day_dtl_inc)
            )
            ,typ as (
            select
                    data_dt
            ,emp_cd
            ,group_concat(distinct leave_typ_desc) as leave_typ_desc
    from a1
    group by
    data_dt
  ,emp_cd
)
        ,a2 as (
            select
                    t1.data_dt
            ,t1.emp_cd
            ,data_week
            ,t2.leave_typ_desc as day_leave_typ_dscs
            ,group_concat(concat(leave_time,replace(rn,rn,''''))) as leave_time
  ,cast(cast(sum(leave_dur_hours)  as decimal(18,1)) as string) as leave_dur_hours
  ,concat(''['',group_concat(day_leave_info,'',''),'']'') as day_leave_info
    from a1 t1
    left join typ t2 on t1.data_dt = t2.data_dt and t1.emp_cd = t2.emp_cd
    group by
    t1.data_dt
  ,t1.emp_cd
  ,data_week
  ,t2.leave_typ_desc
)
    select
            data_dt
            ,emp_cd
  ,day_leave_typ_dscs as leave_typ_desc
  ,concat(''{"data_dt":"''             ,substr(data_dt,5,4),
        ''","data_week":"''          ,data_week,
                ''","day_leave_typ_dscs":"'' ,day_leave_typ_dscs,
                ''","day_leave_info":''      ,day_leave_info,''}'') as leave_info
                ,leave_time
                ,leave_dur_hours
        from a2"
        Position: 59

    }
